﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;

namespace Agrond.Connection
{
    public class MsSQL
    {
        private SqlCommand MsSQLCmd;
        private SqlConnection MsSQLCnn;
        private SqlDataAdapter da;
        private DataSet ds = new DataSet();
        public string ConnectionString { get; set; }

        /* This Function use to dispose connect and close connect*/
        private void DisposeConnect()
        {
            MsSQLCnn.Dispose();
            MsSQLCnn.Close();
        }
        //This Function use to make connect to databse 
        private void ConnectMsSQL()
        {
            try
            {
                MsSQLCnn = new SqlConnection();
                MsSQLCnn.ConnectionString = ConnectionString;
                MsSQLCnn.Open();
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                DisposeConnect();
                //show error
            }
        }

        /// <summary>
        /// This Function use to execute SQL scalar
        /// </summary>
        /// <param name="strConnect">Connection's String</param>
        /// <param name="strQuery">Query's String</param>
        /// <param name="connType">Type of conection number</param>
        /// <returns>this function return true if connection is executed successfully,It will return false</returns>
        public Boolean ExecutrScalarQuery(String query)
        {
            //open Connect to Mssql
            ConnectMsSQL();
            try
            {
                MsSQLCmd = MsSQLCnn.CreateCommand();
                MsSQLCmd.CommandText = Query;
                MsSQLCmd.ExecuteNonQuery();
                DisposeConnect();
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                DisposeConnect();
                return false;
            }
            return true;
        }
        /// <summary>
        /// This Function use to give data form database to datatable
        /// </summary>
        /// <param name="Query">string that contain query</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteQueryReturnDataTable(String query)
        {
            DataTable dtTable = new DataTable();
            //open Connect to Mssql
            ConnectMsSQL();
            try
            {
                MsSQLCmd = MsSQLCnn.CreateCommand();
                MsSQLCmd.CommandText = Query;
                da = new SqlDataAdapter(MsSQLCmd);
                da.Fill(dtTable);
                da.Dispose();
                DisposeConnect();
                return dtTable;
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                DisposeConnect();
            }
            return dtTable;
        }
        /// <summary>
        ///  This function is used to check the value in DataTable
        /// </summary>
        /// <param name="tableObject">The oject of Datatable</param>
        /// <returns>value true if data is null</returns>
        public Boolean IsDataNull(DataTable tableObject)
        {
            if (tableObject.Rows.Count == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// This function execute sp with parameter
        /// </summary>
        /// <param name="storeProceduce"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public Boolean ExecuteStoreproceduce(String storeProceduce,Dictionary<String,String> parameter)
        {
            //open Connect to Mssql
            ConnectMsSQL();
            try
            {
                MsSQLCmd = MsSQLCnn.CreateCommand();
                MsSQLCmd.CommandType = CommandType.StoredProcedure;
                MsSQLCmd.CommandText = storeProceduce;
                foreach (KeyValuePair<String, String> pair in parameter)
                {
                    MsSQLCmd.Parameters.AddWithValue(pair.Key, pair.Value);
                }
                MsSQLCmd.ExecuteNonQuery();
                DisposeConnect();
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                DisposeConnect();
                return false;
            }
            return true;
        }
        /// <summary>
        /// This function execute sp with parameter and return the DataTable that contain the data
        /// </summary>
        /// <param name="storeProceduce">Name or store proceduce</param>
        /// <param name="parameter"> parameter of sp</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteSpReturnDataTable(String storeProceduce,Dictionary<String,String> parameter)
        {
            DataTable dtbl = new DataTable();
            //open Connect to Mssql
            ConnectMsSQL();
            try
            {
                MsSQLCmd = MsSQLCnn.CreateCommand();
                MsSQLCmd.CommandText = storeProceduce;
                MsSQLCmd.CommandType = CommandType.StoredProcedure;
                foreach (KeyValuePair<String, String> pair in parameter)
                {
                    MsSQLCmd.Parameters.AddWithValue(pair.Key, pair.Value);
                }
                da = new SqlDataAdapter(MsSQLCmd);
                da.Fill(dtbl);
                da.Dispose();
                DisposeConnect();
                return dtbl;
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                DisposeConnect();
            }
            return dtbl;
        }
    }
}
